﻿--获取职级异动员工信息
CREATE proc [dbo].[proc_Employee_CompayMove_GetItem]
	@id int
as
	declare @eid int
	set @eid = (select top 1 eid from Employee_CompanyMove where id=@id)

	--select
	--	top 1
	--	Id,
	--	(select ENumber from Employee E where EId=E.Id) as ENumber,
	--	(select ETempNumber from Employee E where EId=E.Id) as ETempNumber,
	--	EId,
	--	(select EName from Employee E where EId=E.Id) as EName,
	--	(select Sex from Employee E where EId=E.Id) as Sex,
	--	(select IDCard from Employee E where EId=E.Id) as IDCard,
	--	(select CompanyName from Company where id=(select Acompanyid from employee E where Id=@eid)) as ACompanyName,
	--	(select CompanyName from Company where id=(select Bcompanyid from employee E where Id=@eid)) as BCompanyName,
	--	(select DName from Department where id=(select Adepartmentid from employee E where Id=@eid)) as ADepartmentName,
	--	(select DName from Department where id=(select Bdepartmentid from employee E where Id=@eid)) as BDepartmentName,
	--	AZhiWu,
	--	BZhiWu,
	--	Reason,
	--	(select EName from Employee E where Proposer=E.UserName) as Proposer,
	--	ApplionTime,
	--	Audit,
	--	Memo,
	--	(select EName from Employee E where AuditPerson=E.UserName) as AuditPerson,
	--	AuditTime,
	--	ISNULL(Execdate,'1900-01-01') AS Execdate,
	--	ISNULL(IsExec,0) AS IsExec,
	--	ISNULL(BPid,0) AS BPid,
	--	(Select p.Title From Province p  where p.Id=BPid) as BProvince,
	--	ISNULL(BCid,0) AS BCid,
	--	ISNULL((Select c.Title From city c  where c.Id=BCid),'') as BCity,
	--	ISNULL(APid,0) AS APid,
	--	ISNULL((Select p.Title From Province p  where p.Id=APid),'') as AProvince,
	--	ISNULL(ACid,0) AS ACid,
	--	ISNULL((Select c.Title From city c  where c.Id=ACid),'') as ACity


	--from Employee_CompanyMove
	--where id=@id
	
	
	select
		top 1
		Id,
		(select ENumber from Employee E where EId=E.Id) as ENumber,
		(select ETempNumber from Employee E where EId=E.Id) as ETempNumber,
		EId,
		(select EName from Employee E where EId=E.Id) as EName,
		(select Sex from Employee E where EId=E.Id) as Sex,
		(select IDCard from Employee E where EId=E.Id) as IDCard,
		(case ACompanyName when '' then (select CompanyName from Company where id=(select Acompanyid from employee E where Id=@eid)) else ACompanyName end) as ACompanyName,
		(case BCompanyName when '' then (select CompanyName from Company where id=(select Bcompanyid from employee E where Id=@eid)) else BCompanyName end ) as BCompanyName,
		(case ADepartmentName when '' then (select DName from Department where id=(select Adepartmentid from employee E where Id=@eid)) else ADepartmentName end) as ADepartmentName,
		(case BDepartmentName when '' then (select DName from Department where id=(select Bdepartmentid from employee E where Id=@eid)) else BDepartmentName end) as BDepartmentName,
		AZhiWu,
		BZhiWu,
		Reason,
		(select EName from Employee E where Proposer=E.UserName) as Proposer,
		ApplionTime,
		Audit,
		Memo,
		(select EName from Employee E where AuditPerson=E.UserName) as AuditPerson,
		AuditTime,
		ISNULL(Execdate,'1900-01-01') AS Execdate,
		ISNULL(IsExec,0) AS IsExec,
		ISNULL(BPid,0) AS BPid,
		(Select p.Title From Province p  where p.Id=BPid) as BProvince,
		ISNULL(BCid,0) AS BCid,
		ISNULL((Select c.Title From city c  where c.Id=BCid),'') as BCity,
		ISNULL(APid,0) AS APid,
		ISNULL((Select p.Title From Province p  where p.Id=APid),'') as AProvince,
		ISNULL(ACid,0) AS ACid,
		ISNULL((Select c.Title From city c  where c.Id=ACid),'') as ACity
,XZJB,
		(case PostName when '' then (select PostName from Post P where id=(select PostId from employee E where Employee_CompanyMove.EId=E.Id)) else PostName end) as PostName,
		(case GradeName when '' then (select Title from Grade G where id=(select GradeId from employee E where Employee_CompanyMove.EId=E.Id)) else GradeName end) as GradeName
,ISNULL((select ename from employee WHERE UserName= AuditPerson),AuditPerson) AS AuditPersonName
	from Employee_CompanyMove
	where id=@id